CREATE view [dbo].[ViewCompanies]                        
as                         
select distinct c.* , u.FirstName+' '+u.LastName as UserNameModifiedBy,                  
p.Description as Province,                
ct.Description as CompanyType,              
e.ID as EnterpriseID,          
s.ID as SupplierID          
from                         
Company c                         
inner join [Security].[User] u on c.UserIDModifiedBy = u.ID                  
inner join Province p on c.ProvinceID = p.ID                
inner join CompanyType ct on c.CompanyTypeID = ct.ID              
left join Enterprise e on c.ID = e.CompanyID          
left join Supplier s on c.ID = s.CompanyID 